The state-of-the-art of iterative development tooling for database development is inadequate as compared to the tooling available for application development. Since the general concepts embodied in application development tools (such as source code control, unit testing, or debugging) are well-understood by the software development community, it is perplexing why equivalent capabilities for database development have not similarly emerged. One reason may stem from the different techniques in which production software (applications and databases) are developed, managed, and enhanced.
In the application world, code is written, a binary is produced, and the binary is put into service. The source code for the product provides an accurate current-state representation of the machine code being executed within the binary. When it is time to modify the product, the source code is appropriately changed and a new binary is produced. Execution of the previous binary is stopped or shutdown, the new binary is inserted in place of the old representation, and execution is restarted. This process has two important characteristics: First, the source code for the modified product still provides an accurate present-state representation of the machine code being executed within the binary (i.e., the source code directly shows the amalgam of the old and new code contained in the binary). Secondly, the upgrade process is a direct “cut and replace” of the old binary with the new.
In contrast, databases represent a class of software that can continue to run even while changes are being applied. Database developers and administrators can take advantage of this capability to modify database systems while running so as to minimize the impact of database availability on users. Initially, database code is written and, when executed, the code creates the first version of the operational database system. At the moment of creation, the database source code provides an accurate offline representation of the system being executed. Since changes to database software are performed while the system is running, updated database systems are seldom produced by creating a new database from scratch that replaces the old database. Most commonly, new code is written that modifies the old database system to the desired new state. At this point, the database source code (the original creation code plus the modification code) no longer provides a direct current-state representation of the database system being executed. As a result, the source code-based tooling concepts used in application development, which depend on the source code declaring the present state of the executing system, cannot be readily applied to database software development.
To enable the ability to modify a database system while it is running, the database management system (DBMS) itself must provide a means of changing the system. For instance, the use of ALTER statements typifies the support provided in common systems. More-advanced support for limited refactoring operations may also be provided, where refactoring is the process of updating the behavioral aspects of the database system. For example, one style of SQL Server enables the ability to rename a table column in an existing system by making a call to a rename function. In this example, the degree of refactoring support is limited and does not update references to the renamed object by programmability objects. Nonetheless, the system function enables an otherwise-impossible action, which in this example is the preservation of the data represented by the column. Tools can significantly improve upon the built-in refactoring facilities of a DBMS. Improvements include finding other objects impacted by the refactoring, and appropriately updating them to reflect the change. This type of support is still inadequate however for modern systems that evolves over time.